var express = require('express');
var router = express.Router();
const jsdom = require("jsdom");
const {JSDOM} = jsdom;
const {window} = new JSDOM(`<!DOCTYPE html>`);
const $ = require('jQuery')(window);
const db=require('../db/db');
const common=require('../lib/common');
var sd = require('silly-datetime');
const moment = require('moment');
const Token=require('../lib/token')
//是否有收藏
function checkColletion(sql,s,id){
    for (let key in s) {
        db.query(sql,[s[key].id,id],function (err,r) {
            if (err) {
                console.log(err);
                return;
            } else {
                if (r.length!=0){
                    s[key].iscolletion=1
                }else {
                    s[key].iscolletion=0
                }
            }
        })
    }
    return s
}

//是否有收藏
function checkColletion2(sql,s,id,type){
    for (let key in s) {
        db.query(sql,[s[key].id,id,type],function (err,r) {
            if (err) {
                console.log(err);
                return;
            } else {
                if (r.length!=0){
                    s[key].iscolletion=1
                }else {
                    s[key].iscolletion=0
                }
            }
        })
    }
    return s
}

//查看是否有关注
function checkConcern(sql,s,id){
    for (let key in s) {
        db.query(sql,[s[key].id,id,],function (err,r) {
            if (err) {
                console.log(err);
                return;
            } else {
                if (r.length!=0){
                    s[key].isfocus=1
                }else {
                    s[key].isfocus=0
                }
            }
        })
    }
    return s
}

//添加合集里的商品
function addShops(sql,s,id,pos,pageSize,user_address){
    let p= () => {}
    let sql2='update array set hot = hot + 1  where id=? '
    db.query(sql2,id,function (err,result) {
        if (err) {
            console.log(err);
            return;
        } else {
            db.query(sql,[id,pos,pageSize],function (err,r) {
                if (err) {
                    console.log(err);
                    return;
                } else {
                    if (r.length==0){
                        s[0].shops=[]
                    }else {
                        s[0].shops=r
                        var s2=r
                        sql =`select * from collection where shop_id=? and user_address=? and type='shop'`
                        p = new Promise((resolve, reject) =>{
                            s2=checkColletion(sql,s2,user_address)
                            setTimeout(() => {
                                resolve(s2)
                            }, 100);
                        });
                        p.then((res) => {
                            s[0].shops=res
                        })
                    }
                }
            })
        }
    })
    return s
}

function realdate(date){
    date=sd.format(new Date(date), 'YYYY-MM-DD')
    return date
}

function realtime(date){
    date=sd.format(new Date(date), 'YYYY-MM-DD HH:mm:ss')
    return date
}

//处理日期函数
function dealdate(s){
    for (let key in s) {
        s[key].staDay=realdate(s[key].staDay)
    }
    return s
}

function dealtime(s){
    for (let key in s){
        s[key].starttime=realtime(s[key].starttime)
        s[key].endtime=realtime(s[key].endtime)
    }
    return s
}


//获取所有有商品正在出售的合集
router.post('/getAllArray', function (req,r,next) {
    let {pageIndex,pageSize}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            r.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    let p= () => {}
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql=`select a.*,'array' as type,u.logo as user_logo,u.nickname as nickname  from array a left join user u on a.owner=u.user_address where (select count(s.id) from shop s where s.array_id=a.id and s.status="selling")>0 order by a.id  desc  limit ?,?`;
    db.query(sql,[pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type='array'`
            p = new Promise((resolve, reject) =>{
                s=checkColletion(sql,s,user_address)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            sql =`select count(a.id) as sum from array a where (select count(s.id) from shop s where s.array_id=a.id and s.status="selling")>0`
            db.query(sql,function (err,result) {
                if(err){
                    console.log(err);
                    r.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    r.json({data:res,count:result[0].sum})
                }
            })
        })

    })
});

//自己正在审核和审核失败的商品
router.post('/getMyShopByAudit', function(req, res, next) {
    let {pageIndex,pageSize,status}=req.body
    if (status=="审核中"){
        status="audit"
    }else if (status=="审核成功"){
        status="success"
    }else if (status=="审核失败"){
        status="refushed"
    }else {
        res.send({flag:false,msg:"非法访问"})
        return;
    }
    let user_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        res.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        res.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where  s.nft_id is null and s.creator_address=? and status=? order by s.id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[user_address,status,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'获取失败'});
            return;
        }else{
            sql =`select count(id) as sum from shop where  nft_id is null and creator_address=? and status=?`
            db.query(sql,[user_address,status],function (err,re) {
                if(err){
                    console.log(err);
                    res.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    res.json({data:result,count:re[0].sum})
                }
            })
        }
    })
});

//自己铸造成功的nft商品
router.post('/getMyNft', function(req, res, next) {
    let {pageIndex,pageSize}=req.body
    let user_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        res.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        res.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null  and s.creator_address =?    order by s.id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[user_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'获取失败'});
            return;
        }else{
            sql =`select count(id) as sum from shop where creator_address=? and nft_id is not null `
            db.query(sql,[user_address],function (err,re) {
                if(err){
                    console.log(err);
                    res.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    res.json({data:result,count:re[0].sum})
                }
            })
        }
    })
});

// 获取自己创建的所有合集
router.post('/getMyArray', function(req, res, next) {
    let {pageIndex,pageSize}=req.body
    let user_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        res.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        res.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select * from array where owner=? order by id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[user_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'获取失败'});
            return;
        }else{
            sql =`select count(id) as sum from array where owner=?`
            db.query(sql,[user_address],function (err,re) {
                if(err){
                    console.log(err);
                    res.send({flag:false,msg:'请求失败'});
                    return;
                }else {
                    res.json({data:result,count:re[0].sum})
                }
            })
        }
    })
});

//自己正在售卖的商品
router.post('/getMySelling', function(req, res, next) {
    let {pageIndex,pageSize}=req.body
    let creator_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        creator_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        res.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        res.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select  s.id, s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address or u.user_address=s.owner  where s.nft_id is not null and s.status='selling' and (s.creator_address =? or s.owner=? ) group by s.id order by s.id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[creator_address,creator_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'获取失败'});
            return;
        }else{
            sql =`select count(distinct s.id) as sum from shop s left join user u on  u.user_address=s.creator_address or u.user_address=s.owner  where s.nft_id is not null and s.status='selling' and (s.creator_address =? or s.owner=? ) `
            db.query(sql,[creator_address,creator_address],function (err,re) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    res.json({data:result,count:re[0].sum})
                }
            })
        }
    })
});

//我的喜欢作品
router.post('/getMyColletion', function(req, r, next) {
    let {pageIndex,pageSize,type}=req.body
    let user_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        r.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        r.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql="select s.* ,? as type from collection c left join "+type+" s on c.shop_id=s.id WHERE c.type=? and c.user_address=? order by c.id desc limit ?,?";
    if (type=="shop"){
        sql="select s.* ,? as type,\n" +
            "(select logo from user u where u.user_address=s.creator_address) as user_logo,\n"+
            "(select nickname from user u where u.user_address=s.creator_address) as nickname\n"+
            "from collection c left join shop s on c.shop_id=s.id WHERE c.type=? and c.user_address=? order by c.id desc limit ?,?";
    }

    db.query(sql,[type,type,user_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            return;
        }else {
            sql ="select count(c.id) as sum from collection c left join "+type+" s on c.shop_id=s.id WHERE c.type=? and c.user_address=?"
            db.query(sql,[type,user_address],function (err,re) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    r.json({data:result,count:re[0].sum})
                }
            })
        }

    })
});

//加入或者取消单品收藏
router.post('/collete', function(req, res, next) {
    let {shop_id,type}=req.body
    let user_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    let sql=''
    if (type=='shop'){
        sql=`select * from shop where id=? and creator_address=?`
    }else if (type=='array'){
        sql=`select * from array where id=? and owner=?`
    }
    db.query(sql,[user_address,shop_id],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'不能添加自己的商品'});
            return;
        }else {
            if (result.length >0) {
                res.send({flag:false,msg:'不能添加自己的商品'});
                return;
            }else {
                sql=`select * from collection where user_address=? and shop_id=? and type=?`
                db.query(sql,[user_address,shop_id,type],function (err,result) {
                    if(err){
                        console.log(err);
                        res.send({flag:false,msg:'不能添加自己的商品'});
                        return;
                    }else{
                        if (result.length==0){
                            sql=`insert into collection(user_address,shop_id,type) values(?,?,?)`;
                            db.query(sql,[user_address,shop_id,type],function (err,result) {
                                if(err){
                                    console.log(err);
                                    res.send({flag:false,msg:'收藏失败'});
                                    return;
                                }else {
                                    sql = "update " + type + " set hot = hot + 5 where id=" + shop_id
                                    db.query(sql, function (err, result) {
                                        if (err) {
                                            console.log(err);
                                            res.send({flag: false, msg: '收藏失败'});
                                            return;
                                        } else {
                                            res.send({flag: true, msg: '收藏成功'});
                                        }
                                    })
                                }
                            })
                        }else{
                            sql=`delete from collection where id=?`;
                            db.query(sql,result[0].id,function (err,result) {
                                if(err){
                                    console.log(err);
                                    res.send({flag:false,msg:'取消收藏失败'});
                                    return;
                                }else{
                                    sql = "update " + type + " set hot = hot - 5 where id=" + shop_id
                                    db.query(sql, function (err, result) {
                                        if (err) {
                                            console.log(err);
                                            res.send({flag: false, msg: '收藏失败'});
                                            return;
                                        } else {
                                            res.send({flag:true,msg:'取消收藏'});
                                        }
                                    })

                                }
                            })
                        }
                    }
                })
            }
        }
    })
});

//获取搜索商品和用户
router.post('/getSearch', function(req, r, next) {
    let {pageIndex,pageSize,keyword,type}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            r.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    if (type==null){
        r.send({flag:false,msg:'请求失败'});
        return;
    }else if(type.trim()==null){
        r.send({flag:false,msg:'请求失败'});
        return;
    }
    if (keyword==null){
        r.send({flag:false,msg:'搜索文字为空'});
        return;
    }else if (keyword.trim()==""){
        r.send({flag:false,msg:'搜索文字为空'});
        return;
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let p= () => {}
    let sql=""
    if (type=='shop'){
        sql=`select s.*,'shop' as type ,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address where s.title like'%`+keyword+`%'  and status='selling' order by s.id desc limit ?,?`;
    }else if(type=='array') {
        sql=`select *,'array' as type  from array where title like'%`+keyword+`%'  order by id desc limit ?,?`;
    }else if (type=='user'){
        sql=`select *,'user' as type  from user where nickname like'%`+keyword+`%'  order by id desc limit ?,?`;
    }
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'搜索失败'});
            return;
        }else{
            var s=result
            if (type=='shop'){
                sql=`select * from collection where shop_id=? and user_address=? and type='shop'`;
            }else if (type=='array'){
                sql=`select * from collection where shop_id=? and user_address=? and type='array'`;
            }else {
                sql='select * from focus_user where user_id=? and focus_user_id=?'
            }
            p = new Promise((resolve, reject) =>{
                if (type=='user'){
                    s=checkConcern(sql,s,user_address)
                }else {
                    s=checkColletion2(sql,s,user_address,type)
                }
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            if (type=='shop'){
                sql=`select count(id) as sum from shop where title like'%`+keyword+`%'  `;
            }else if(type=='array') {
                sql=`select  count(id) as sum  from array where title like'%`+keyword+`%' `;
            }else if (type=='user'){
                sql=`select  count(id) as sum  from user where nickname like'%`+keyword+`%' `;
            }
            db.query(sql,function (err,result) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    r.json({data:res,count:result[0].sum})
                }
            })
        })
    })
});

//添加商品
router.post('/putShop', function(req, res, next) {
    let {title,classify,shop_hash,cover_hash,info,array_id,commission}=req.body
    let creator_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        creator_address=data.data
    }
    if (title==null||classify==null||shop_hash==null||cover_hash==null||info==null||creator_address==null||commission==null){
        res.send({flag:false,msg:'商品信息某处为空'});
        return
    }else if (title.trim()==null||classify.trim()==null||shop_hash.trim()==null||cover_hash.trim()==null||info.trim()==null||creator_address.trim()==null||commission.trim()==null){
        res.send({flag:false,msg:'商品信息某处为空'});
        return
    }
    let status="audit"
    let hot=0
    let  sql=''
    if (array_id==null||array_id==""){
        sql=`insert into shop(title,classify,shop_hash,owner,cover_hash,info,creator_address,status,hot,commission) values(?,?,?,?,?,?,?,?,?,?)`
    }else {
        sql="insert into shop(title,classify,shop_hash,owner,cover_hash,info,creator_address,status,hot,commission,array_id) values(?,?,?,?,?,?,?,?,?,?,?)"
    }
    db.query(sql,[title,classify,shop_hash,creator_address,cover_hash,info,creator_address,status,hot,commission,array_id],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'上传失败'});
            return;
        }else{
            if (result.length<1){
                res.send({flag:false,msg:'上传失败'});
                return;
            }
            res.send({flag:true,msg:'上传成功'});
        }
    })
});

//查看自己所买的商品
router.post('/getMySold', function(req, res, next) {
    let {pageSize,pageIndex}=req.body
    let user_address=""
    const token=String(req.headers.authorization).split(' ').pop()
    if (token=="undefined"){
        res.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        res.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        res.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        res.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select s.* ,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address where s.owner = ? and s.status='sold' order by s.id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[user_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            res.send({flag:false,msg:'获取失败'});
            return;
        }else{
            sql=`select count(id) as sum from shop where owner = ? and status='sold'`
            db.query(sql,[user_address],function (err,re) {
                if(err){
                    console.log(err);
                    res.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    res.json({data:result,count:re[0].sum})
                }
            })
        }
    })
});

//访问单个商品详情
router.post('/getShopById', function(req, res, next) {
    const {id}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            res.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    let sql="select s.*,\n" +
        "(select count(id) from collection where shop_id=? and user_address=? and type='shop') as iscolletion ,\n" +
        "(select u.nickname from user u where u.user_address=s.creator_address) as creator_name,\n" +
        "(select u.logo from user u where u.user_address=s.creator_address) as creator_logo,\n" +
        "(select u.nickname from user u where u.user_address=s.`owner`) as owner_name,\n" +
        "(select u.logo from user u where u.user_address=s.`owner`) as owner_logo,\n" +
        "(select a.logo from array a where a.id=s.array_id) as array_logo ,\n" +
        "(select a.title from array a where a.id=s.array_id) as array_name,\n" +
        "(SELECT COUNT(c.id) FROM collection c where c.shop_id=s.id) as collection_num \n" +
        "from\n" +
        "shop s where s.id=?"
    db.query(sql,[id,user_address,id],function (err,result) {
        if (err){
            console.log(err)
            return
        }else{
            sql=`update shop set hot = hot + 1  where id=? `
            db.query(sql,id,function (err,r){
                if (err){
                    console.log(err)
                    res.send({flag:false,msg:'获取失败'});
                    return
                }else {
                    if (result.length<1){
                        res.send({flag:false,msg:'获取失败'});
                        return;
                    }
                    result[0].put_time=realtime(result[0].put_time)
                    res.json(result)
                }
            })
        }
    })
});

//访问某个合集的信息和商品
router.post('/getArrayDetailById', function (req,r,next) {
    let {id,pageSize,pageIndex,classify,form_of_sale,minprice,maxprice}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            r.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let p= () => {}
    let pos=(pageIndex-1)*pageSize;
    let sql="select a.*, \n" +
        "(select count(id) from collection where shop_id=? and user_address=? and type='array') as iscolletion ,\n" +
        "u.logo as owner_logo,\n" +
        "u.nickname as owner_nickname,\n" +
        "(SELECT count(f.id) FROM focus_user f where f.focus_user_id=a.`owner`) as number_fans,\n" +
        "(SELECT count(s.id) FROM shop s where s.creator_address=a.`owner` and s.nft_id is not null) as number_nft,\n" +
        "(SELECT count(f.id) FROM focus_user f where f.user_id=a.`owner`) as number_concerns\n" +
        "from array a \n" +
        "LEFT JOIN user u on u.user_address=a.`owner`\n" +
        "where a.id=?";
    db.query(sql,[id,user_address,id],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            if (result.length<1){
                r.send({flag:false,msg:'获取失败'});
                return;
            }
            if (form_of_sale=='所有'&&classify=='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.price >="+minprice+" and s.price<="+maxprice+" and s.array_id=? order by s.hot desc limit ?,?"
                }else {
                    sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.array_id=? order by s.hot desc limit ?,?`;
                }
            }else if(form_of_sale=='所有'&&classify!='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.classify='"+classify+ "'  and s.price >="+minprice+" and s.price<="+maxprice+" and s.array_id=? order by s.hot desc limit ?,?"
                }else {
                    sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.classify='"+classify+ "'   and s.array_id=? order by s.hot desc limit ?,?";
                }
            }else if(classify=='所有'&&form_of_sale!='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='"+form_of_sale+"'  and s.price >="+minprice+" and s.price<="+maxprice+" and s.array_id=? order by s.hot desc limit ?,?";
                }else {
                    sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='`+form_of_sale+`' and s.array_id=? order by s.hot desc limit ?,?`;
                }
            }else if (classify!='所有'&&form_of_sale!='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='"+form_of_sale+"' and s.classify='"+classify+"'  and s.price >="+minprice+" and s.price<="+maxprice+"  and s.array_id=? order by s.hot desc limit ?,?";
                }else {
                    sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='`+form_of_sale+`' and s.classify='`+classify+`'  and s.array_id=? order by s.hot desc limit ?,?`;
                }
            }
            var s=result
            p = new Promise((resolve, reject) =>{
                s=addShops(sql,s,id,pos,pageSize,user_address)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            if (form_of_sale=='所有'&&classify=='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and status='selling' and price >="+minprice+" and price<="+maxprice+" and array_id=? "
                }else {
                    sql=`select count(id) as sum from shop  where nft_id is not null and status='selling'  and array_id=?`;
                }
            }else if(form_of_sale=='所有'&&classify!='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and classify='"+classify+ "' and status='selling' and price >="+minprice+" and price<="+maxprice+" and array_id=?"
                }else {
                    sql="select count(id) as sum from shop  where nft_id is not null and classify='"+classify+ "' and status='selling'  and array_id=? ";
                }
            }else if(classify=='所有'&&form_of_sale!='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and form_of_sale='"+form_of_sale+"' and status='selling' and price >="+minprice+" and price<="+maxprice+" and array_id=? ";
                }else {
                    sql=`select count(id) as sum from shop  where nft_id is not null and form_of_sale='`+form_of_sale+`' and status='selling'  and array_id=? `;
                }
            }else if (classify!='所有'&&form_of_sale!='所有'){
                if (minprice!=null||maxprice!=null||minprice!=""||maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and form_of_sale='"+form_of_sale+"' and classify='"+classify+"' and status='selling' and price >="+minprice+" and price<="+maxprice+"  and array_id=? ";
                }else {
                    sql=`select count(id) as sum from shop  where nft_id is not null and form_of_sale='`+form_of_sale+`' and classify='`+classify+`' and status='selling'  and array_id=? `;
                }
            }
            db.query(sql,[id],function (err,result) {
                if(err){
                    console.log(err);
                    r.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    r.json({data:res,count:result[0].sum})
                }
            })
        })
    })

});

//展示轮播图
router.get('/getAd', function(req, res, next) {
    let sql="SELECT  *,\n" +
        "(SELECT title from array where id=array_id) as array_name,\n" +
        "(SELECT logo from array where id=array_id) as array_img,\n" +
        "(SELECT logo from user where user_address=creator_address) as creator_logo,\n" +
        "(SELECT logo from user where user_address=owner) as owner_logo,\n" +
        "(SELECT nickname from user where user_address=creator_address) as creator_nickname,\n" +
        "(SELECT nickname from user where user_address=owner) as owner_nickname\n" +
        "FROM shop where nft_id is not null  ORDER BY hot DESC LIMIT 4"
    db.query(sql,function (err,result) {
        if (err){
            console.log(err)
            res.send({flag:false,msg:'获取失败'});
            return
        }else{
            if (result.length<1){
                res.send({flag:false,msg:'获取失败'});
                return;
            }
            res.json(result)
        }
    })
});

//热门分合集和商品
router.post('/getShopByHot', function (req,r,next) {
    let {pageIndex,pageSize,type,form_of_sale,classify,minprice,maxprice}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            res.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    let p= () => {}
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    if (minprice==null||minprice==""){
        if (maxprice!=null||maxprice!=""){
            r.send({flag:false,msg:'价格区间格式不对'});
            return;
        }
    }else {
        if (maxprice==null||maxprice==""){
            r.send({flag:false,msg:'价格区间格式不对'});
            return;
        }
    }
    if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
        var n1= Number(minprice);
        var n2=Number(maxprice)
        if (isNaN(n1)||n1<0||isNaN(n2)||n2<0)
        {
            r.send({flag:false,msg:'价格区间格式不对'});
            return;
        }
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql=""
    if (type=='shop'){
        if (form_of_sale=='所有'&&classify=='所有'){
            if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.price >="+minprice+" and s.price<="+maxprice+" order by s.hot desc limit ?,?"
            }else {
                sql=`select *,'shop' as type from shop  where nft_id is not null and status='selling' order by hot desc limit ?,?`;
            }
        }else if(form_of_sale=='所有'&&classify!='所有'){
            if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.price >="+minprice+" and s.price<="+maxprice+" order by s.hot desc limit ?,?"
            }else {
                sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.classify='"+classify+ "' order by s.hot desc limit ?,?";
            }
        }else if(classify=='所有'&&form_of_sale!='所有'){
            if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='"+form_of_sale+"'  and s.price >="+minprice+" and s.price<="+maxprice+" order by s.hot desc limit ?,?";
            }else {
                sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='`+form_of_sale+`' order by s.hot desc limit ?,?`;
            }
        }else if (classify!='所有'&&form_of_sale!='所有'){
            if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='"+form_of_sale+"' and s.classify='"+classify+"'  and s.price >="+minprice+" and s.price<="+maxprice+" order by s.hot desc limit ?,?";
            }else {
                sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='`+form_of_sale+`' and s.classify='`+classify+`'  order by s.hot desc limit ?,?`;
            }
        }
    }else {
        sql=`select a.*,'array' as type from array a where (select count(s.id) from shop s where s.array_id=a.id and s.status="selling")>0 order by a.hot  desc  limit ?,?`;
    }
    db.query(sql,[pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type=?`
            p = new Promise((resolve, reject) =>{
                s=checkColletion2(sql,s,user_address,type)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            if (type=='shop'){
                if (form_of_sale=='所有'&&classify=='所有'){
                    if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                        sql="select count(id) as sum from shop  where nft_id is not null and status='selling' and price >="+minprice+" and price<="+maxprice
                    }else {
                        sql=`select count(id) as sum from shop  where nft_id is not null and status='selling'`;
                    }
                }else if(form_of_sale=='所有'&&classify!='所有'){
                    if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                        sql="select count(id) as sum from shop  where nft_id is not null and classify='"+classify+ "' and status='selling' and price >="+minprice+" and price<="+maxprice
                    }else {
                        sql="select count(id) as sum from shop  where nft_id is not null and classify='"+classify+ "' and status='selling'";
                    }
                }else if(classify=='所有'&&form_of_sale!='所有'){
                    if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                        sql="select count(id) as sum from shop  where nft_id is not null and form_of_sale='"+form_of_sale+"' and status='selling' and price >="+minprice+" and price<="+maxprice;
                    }else {
                        sql=`select count(id) as sum from shop  where nft_id is not null and form_of_sale='`+form_of_sale+`' and status='selling'`;
                    }
                }else if (classify!='所有'&&form_of_sale!='所有'){
                    if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                        sql="select count(id) as sum from shop  where nft_id is not null and form_of_sale='"+form_of_sale+"' and classify='"+classify+"' and status='selling' and price >="+minprice+" and price<="+maxprice;
                    }else {
                        sql=`select count(id) as sum from shop  where nft_id is not null and form_of_sale='`+form_of_sale+`' and classify='`+classify+`' and status='selling'`;
                    }
                }
            }else {
                sql=`select count(a.id) as sum from array a where (select count(s.id) from shop s where s.array_id=a.id and s.status="selling")>0  `;
            }
            db.query(sql,function (err,re) {
                if(err){
                    console.log(err);
                    r.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })

    })
});

//获取拍卖商品
router.post('/getShopByAuction', function(req, r, next) {
    let {pageIndex,pageSize}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            r.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex)
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql="select s.*,'auction' as type, \n" +
        "(select u.logo from user u where u.user_address=s.creator_address) as user_logo,\n"+
        "(select u.nickname from user u where u.user_address=s.creator_address) as nickname\n"+
        "from shop s left JOIN auction a\n" +
        "on s.auction_id=a.id\n" +
        "where a.`status`='ing' and s.`status`='selling' and s.form_of_sale='拍卖' and s.auction_id is not null\n" +
        "order by s.id desc limit ?,?";
    db.query(sql,[pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            //可以为空
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type='shop'`
            p = new Promise((resolve, reject) =>{
                s=checkColletion(sql,s,user_address)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            sql="select count(s.id) as sum \n" +
                "from shop s left JOIN auction a\n" +
                "on s.auction_id=a.id\n" +
                "where a.`status`='ing' and s.`status`='selling' and s.form_of_sale='拍卖' and s.auction_id is not null\n"
            db.query(sql,function (err,re) {
                if(err){
                    console.log(err);
                    r.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })
    })
});

//某拍卖商品详情
router.post('/getShopByIdAndAuction', function(req, r, next) {
    let {id}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            res.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    if (id==null){
        r.send({flag:false,msg:'获取失败'});
        return
    }else if (id.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return
    }
    let sql="SELECT s.*,a.status,a.endtime,a.starttime,a.title as aution_title,\n" +
        "(select u.logo from user u where u.user_address=s.hightester) as hightester_logo,\n" +
        "(select u.nickname from user u where u.user_address=s.hightester) as hightester_name,\n" +
        "(select count(id) from collection where shop_id=? and user_address=? and type='shop') as iscolletion ,\n" +
        "(select u.nickname from user u where u.user_address=s.creator_address) as creator_name,\n" +
        "(select u.logo from user u where u.user_address=s.creator_address) as creator_logo,\n" +
        "(select u.nickname from user u where u.user_address=s.`owner`) as owner_name,\n" +
        "(select u.logo from user u where u.user_address=s.`owner`) as owner_logo,\n" +
        "(SELECT a.title FROM array a where a.id=s.array_id) as collection_name,\n" +
        "(SELECT COUNT(c.id) FROM collection c where c.shop_id=s.id) as collection_num\n" +
        "FROM shop s  JOIN auction a on s.auction_id=a.id\n" +
        "where s.id=?"
    db.query(sql,[id,user_address,id],function (err,result) {
        if (err){
            console.log(err)
            r.send({flag:false,msg:'获取失败'});
            return
        }else {
            if (result.length<1){
                r.send({flag:false,msg:'获取失败'});
                return
            }
            result[0].starttime=realtime(result[0].starttime)
            result[0].endtime=realtime(result[0].endtime)
            r.json(result)
        }
    })
});

//展示拍卖会后面的有效日期
router.post('/queryDate', function(req, r, next) {
    var date=sd.format(new Date(), 'YYYY-MM-DD HH:mm:ss')
    let p= () => {}
    let sql="SELECT  count(id),cast( starttime as date) AS staDay FROM auction WHERE true AND starttime > ?  and `status`='ready' GROUP BY cast(starttime as date)\n"
    db.query(sql,date,function (err,result) {
        if (err){
            console.log(err)
            r.send({flag:false,msg:'获取失败'});
            return
        }else{
            if (result.length<1){
                r.send({flag:false,msg:'获取失败'});
                return
            }
            var s=result
            p = new Promise((resolve, reject) =>{
                s=dealdate(s)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            r.json(res)
        })
    })
});

//指定日期拍卖会
router.post('/queryAuctionByDate', function(req, r, next) {
    let {date}=req.body
    let flag=/^\d{4}-\d{2}-\d{2}$/.test(date)
    if (!flag){
        r.send({flag:false,msg:'非时间格式'});
        return
    }
    date=sd.format(new Date(date), 'YYYY-MM-DD HH:mm')
    let startTime2 = new Date(new Date(new Date(date).toLocaleDateString()).getTime()); // 当天0点
    let endTime2 = new Date(new Date(new Date(date).toLocaleDateString()).getTime() +24 * 60 * 60 * 1000 -1);// 当天23:59
    let p= () => {}
    let sql="SELECT\n" +
        "*\n" +
        "FROM\n" +
        "auction\n" +
        "WHERE true\n" +
        "AND starttime < ? and starttime > ? and `status`='ready'"
    db.query(sql,[endTime2,startTime2],function (err,result) {
        if (err){
            console.log(err)
            r.send({flag:false,msg:'获取失败'});
            return
        }else{
            if (result.length<1){
                r.send({flag:false,msg:'获取失败'});
                return
            }
            var s=result
            p = new Promise((resolve, reject) =>{
                s=dealtime(s)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            r.json(res)
        })
    })
});

//获取分类和售卖方式的商品
router.post('/getShopByClassifyAndForm', function(req, r, next) {
    let {pageIndex,pageSize,classify,form_of_sale,minprice,maxprice}=req.body
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            r.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    let p= () => {}
    let sql=``
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    if (minprice==null||minprice==""){
        if (maxprice!=null||maxprice!=""){
            r.send({flag:false,msg:'价格区间格式不对'});
            return;
        }
    }else {
        if (maxprice==null||maxprice==""){
            r.send({flag:false,msg:'价格区间格式不对'});
            return;
        }
    }
    if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
        var n1= Number(minprice);
        var n2=Number(maxprice)
        if (isNaN(n1)||n1<0||isNaN(n2)||n2<0)
        {
            r.send({flag:false,msg:'价格区间格式不对'});
            return;
        }
    }

    if (form_of_sale=='所有'&&classify=='所有'){
        if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
            sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.price >="+minprice+" and s.price<="+maxprice+" order by s.id desc limit ?,?"
        }else {
            sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' order by s.id desc limit ?,?`;
        }
    }else if(form_of_sale=='所有'&&classify!='所有'){
        if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
            sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.classify='"+classify+ "'  and s.price >="+minprice+" and s.price<="+maxprice+" order by s.id desc limit ?,?"
        }else {
            sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.classify='"+classify+ "'  order by s.id desc limit ?,?";
        }
    }else if(classify=='所有'&&form_of_sale!='所有'){
        if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
            sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling'and s.form_of_sale='"+form_of_sale+"'  and s.price >="+minprice+" and s.price<="+maxprice+" order by s.id desc limit ?,?";
        }else {
            sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='`+form_of_sale+`'  order by s.id desc limit ?,?`;
        }
    }else if (classify!='所有'&&form_of_sale!='所有'){
        if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
            sql="select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='"+form_of_sale+"' and s.classify='"+classify+"'  and s.price >="+minprice+" and s.price<="+maxprice+" order by s.id desc limit ?,?";
        }else {
            sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null and s.status='selling' and s.form_of_sale='`+form_of_sale+`' and s.classify='`+classify+`'  order by s.id desc limit ?,?`;
        }
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type='shop'`
            p = new Promise((resolve, reject) =>{
                s=checkColletion(sql,s,user_address)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            if (form_of_sale=='所有'&&classify=='所有'){
                if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and status='selling' and price >="+minprice+" and price<="+maxprice
                }else {
                    sql=`select count(id) as sum from shop  where nft_id is not null and status='selling' `;
                }
            }else if(form_of_sale=='所有'&&classify!='所有'){
                if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and classify='"+classify+ "' and status='selling' and price >="+minprice+" and price<="+maxprice
                }else {
                    sql="select count(id) as sum from shop  where nft_id is not null and classify='"+classify+ "' and status='selling'";
                }
            }else if(classify=='所有'&&form_of_sale!='所有'){
                if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and form_of_sale='"+form_of_sale+"' and status='selling' and price >="+minprice+" and price<="+maxprice;
                }else {
                    sql=`select count(id) as sum from shop  where nft_id is not null and form_of_sale='`+form_of_sale+`' and status='selling' order by id desc limit ?,?`;
                }
            }else if (classify!='所有'&&form_of_sale!='所有'){
                if (minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){
                    sql="select count(id) as sum from shop  where nft_id is not null and form_of_sale='"+form_of_sale+"' and classify='"+classify+"' and status='selling' and price >="+minprice+" and price<="+maxprice;
                }else {
                    sql=`select count(id) as sum from shop  where nft_id is not null and form_of_sale='`+form_of_sale+`' and classify='`+classify+`' and status='selling'`;
                }
            }
            db.query(sql,function (err,re) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })
    })
});

//获取自己正在拍卖的东西
router.post('/getMyAucitonByStatus',function (req,res,next){
    let {pageIndex,pageSize,status}=req.body
    if (status=="未拍卖"){
        status="ready"
    }else if (status=="拍卖中"){
        status="ing"
    }else if (status=="已结束"){
        status="end"
    }
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token==""||token=="undefined"){
        user_address=" "
    }else {
        let data = Token.decode(token)
        if (data.status=="failed"){
            res.send({flag:false,msg:data.msg})
            return;
        }else {
            user_address=data.data
        }
    }
    if (pageIndex==null||pageSize==null){
        res.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        res.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex)
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql="select s.*,'auction' as type, \n" +
        "(select u.logo from user u where u.user_address=s.hightester) as hightester_logo,\n" +
        "(select u.nickname from user u where u.user_address=s.hightester) as hightester_name,\n" +
        "(select u.logo from user u where u.user_address=s.creator_address) as user_logo,\n"+
        "(select u.nickname from user u where u.user_address=s.creator_address) as nickname\n"+
        "from shop s left JOIN auction a\n" +
        "on s.auction_id=a.id\n" +
        "where a.`status`=?  and s.creator_address=? and s.form_of_sale='拍卖' and s.auction_id is not null\n" +
        "order by s.id desc limit ?,?";
    db.query(sql,[status,user_address,pos,pageSize],function (err,result) {
        if (err) {
            console.log(err);
            res.send({flag: false, msg: '获取失败'});
            return;
        } else {
            sql="select count(s.id) as sum from shop s left JOIN auction a on s.auction_id=a.id where a.`status`=?  and s.creator_address=? and s.form_of_sale='拍卖' and s.auction_id is not null"
            db.query(sql,[status,user_address],function (err,re) {
                if (err) {
                    console.log(err);
                    res.send({flag: false, msg: '获取失败'});
                    return;
                } else {
                    res.json({data:result,count:re[0].sum})
                }
            })
        }
    })
})



//他人有售卖商品的合集  还要加喜欢
router.post('/getOtherArray', function(req, r, next) {
    let {pageIndex,pageSize,other_address}=req.body
    let type="array"
    let p= () => {}
    if (other_address==null){
        r.send({flag:false,msg:"非法访问"})
        return;
    }
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token=="undefined"){
        r.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        r.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql=`select a.*,'array' as type from array a where (select count(s.id) from shop s where s.array_id=a.id and s.status="selling")>0 and a.owner=? order by a.id  desc  limit ?,?`;
    db.query(sql,[other_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{ //他可能没创建合集
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type=?`
            p = new Promise((resolve, reject) =>{
                s=checkColletion2(sql,s,user_address,type)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            sql=`select count(a.id) as sum from array a where (select count(s.id) from shop s where s.array_id=a.id and s.status="selling")>0 and a.owner=?  `
            db.query(sql,[other_address],function (err,re) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })

    })
});

//他人铸造成功的nft商品  还要加喜欢
router.post('/getOtherNft', function(req, r, next) {
    let {pageIndex,pageSize,other_address}=req.body
    let type="shop"
    let p= () => {}
    if (other_address==null){
        r.send({flag:false,msg:"非法访问"})
        return;
    }
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token=="undefined"){
        r.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        r.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address  where s.nft_id is not null  and s.creator_address =?    order by s.id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[other_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type=?`
            p = new Promise((resolve, reject) =>{
                s=checkColletion2(sql,s,user_address,type)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            sql =`select count(id) as sum from shop where creator_address=? and nft_id is not null `
            db.query(sql,[other_address],function (err,re) {
                if(err){
                    console.log(err);
                    res.send({flag:false,msg:'获取失败'});
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })
    })
});

//他人正在售卖的商品
router.post('/getOtherSelling', function(req, r, next) {
    let {pageIndex,pageSize,other_address}=req.body
    let type="shop"
    let p= () => {}
    if (other_address==null){
        r.send({flag:false,msg:"非法访问"})
        return;
    }
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token=="undefined"){
        r.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        r.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let sql=`select s.*,'shop' as type,u.logo as user_logo,u.nickname as nickname from shop s left join user u on  u.user_address=s.creator_address or u.user_address=s.owner  where s.nft_id is not null and s.status='selling' and (s.creator_address =? or s.owner =? ) group by s.id order by s.id desc limit ?,?`;
    let pos=(pageIndex-1)*pageSize;
    db.query(sql,[other_address,other_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            r.send({flag:false,msg:'获取失败'});
            return;
        }else{
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type=?`
            p = new Promise((resolve, reject) =>{
                s=checkColletion2(sql,s,user_address,type)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            sql =`select count(distinct s.id) as sum from shop s left join user u on  u.user_address=s.creator_address or u.user_address=s.owner  where s.nft_id is not null and s.status='selling' and (s.creator_address =? or s.owner =? ) `
            db.query(sql,[other_address,other_address],function (err,re) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })
    })
});

//他人喜欢的商品
router.post('/getOtherColletion', function(req, r, next) {
    let {pageIndex,pageSize,type,other_address}=req.body
    let p= () => {}
    if (other_address==null){
        r.send({flag:false,msg:"非法访问"})
        return;
    }
    const token=String(req.headers.authorization).split(' ').pop()
    let user_address=""
    if (token=="undefined"){
        r.send({flag:false,msg:"未登录无权限访问"})
        return;
    }
    let data = Token.decode(token)
    if (data.status=="failed"){
        r.send({flag:false,msg:data.msg})
        return;
    }else {
        user_address=data.data
    }
    if (pageIndex==null||pageSize==null){
        r.send({flag:false,msg:'获取失败'});
        return;
    }else if (pageIndex.trim()==""||pageSize.trim()==""){
        r.send({flag:false,msg:'获取失败'});
        return;
    }
    pageIndex=parseInt(pageIndex);
    pageSize=parseInt(pageSize);
    let pos=(pageIndex-1)*pageSize;
    let sql="select s.* ,? as type from collection c left join "+type+" s on c.shop_id=s.id WHERE c.type=? and c.user_address=? order by c.id desc limit ?,?";
    if (type=="shop"){
        sql="select s.* ,? as type,\n" +
            "(select logo from user u where u.user_address=s.creator_address) as user_logo,\n"+
            "(select nickname from user u where u.user_address=s.creator_address) as nickname\n"+
            "from collection c left join shop s on c.shop_id=s.id WHERE c.type=? and c.user_address=? order by c.id desc limit ?,?";
    }
    db.query(sql,[type,type,other_address,pos,pageSize],function (err,result) {
        if(err){
            console.log(err);
            return;
        }else{
            var s=result
            sql =`select * from collection where shop_id=? and user_address=? and type=?`
            p = new Promise((resolve, reject) =>{
                s=checkColletion2(sql,s,user_address,type)
                setTimeout(() => {
                    resolve(s)
                }, 100);
            });
        }
        p.then((res) => {
            sql ="select count(c.id) as sum from collection c left join "+type+" s on c.shop_id=s.id WHERE c.type=? and c.user_address=?"
            db.query(sql,[type,other_address],function (err,re) {
                if(err){
                    console.log(err);
                    return;
                }else {
                    r.json({data:res,count:re[0].sum})
                }
            })
        })
    })
});


router.post('/getParticipateAuction', function(req, r, next) {
    let {user_addres}=req.body
    if (user_addres==null){
        r.send({flag:false,msg:"参数错误"})
        return;
    }
    let sql=`select * from participate_auction where user_addres=? ` ;
    db.query(sql,[user_addres],function (err,result) {
        if(err){
            console.log(err);
            return
        }else{
            if(result.length == 0){
                r.json({data:[]})
                return
            }
            sql="select s.*,a.starttime,a.endtime,a.status from shop s left join auction a on s.auction_id=a.id where nft_id in ("
            for (let i=0;i<result.length-1;i++){
                sql+=new String(result[i].token_id)+","
            }
            sql+=new String(result[result.length-1].token_id)+")"
            db.query(sql,[],function (err,result) {
                if(err){
                    console.log(err);
                    return
                }else{
                    // resultrealtime(date)
                    for(let j=0;j<result.length;j++){
                        result[j].put_time=realtime(result[j].put_time)
                        result[j].starttime=realtime(result[j].starttime)
                        result[j].endtime=realtime(result[j].endtime)
                    }
                    r.json({data:result})
                }
            })

        }
    })
});

module.exports = router;

